package com.tenxun.test;


import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.test.context.SpringBootTest;

import javax.sql.DataSource;
import java.sql.*;
import java.util.*;


@SpringBootTest
public class JdbcDemo {

    @Qualifier("dataSource")
    @Autowired
    private DataSource dataSource;

    @Test
    void jdtest() throws SQLException {
        //1.获取连接
        Connection con=dataSource.getConnection();
        //2.创建Statement
        String sql = "select * from student " +
                     " where name like ?";
        PreparedStatement sta = con.prepareStatement(sql);
        //3.发送sql

        //sta.setInt(1,1);
        String name = "讯";
        sta.setString(1, "%" + name + "%" );
        //ResultSet rs = sta.executeQuery();
        boolean flag = sta.execute();
        ResultSet rs=null;
        if(flag) {
            rs = sta.getResultSet();
            ResultSetMetaData rsmd =rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            //4.处理结果
            List<Map<String, Object>> list = new ArrayList<>();

            while(rs.next()) {
                Map<String, Object> map = new HashMap();
                for (int i = 1; i <= columnCount; i++) {
                    //获取别名Label
                    String colum = rsmd.getColumnLabel(i);
                    map.put(colum,rs.getObject(colum));
                }
                //list.add(map);
                System.out.println(map);
            }

            /*  while (rs.next()) {   //循环一次取一行，一行记录一个map对象
                Map<String, Object> map = new HashMap<>();
                map.put("id", rs.getInt("id"));
                 map.put("name", rs.getString("name"));
                map.put("age", rs.getInt("age"));
                map.put("hiretime", rs.getTimestamp("hiretime"));
                map.put("time", rs.getTimestamp("time"));
                System.out.println(map);
                list.add(map);
            }*/
        }
        //5.释放资源
        rs.close();
        sta.close();
        con.close();
    }

    @Test
    void insertjdbc() throws SQLException {
        Connection  con = dataSource.getConnection();
        Statement statement = con.createStatement();

        String sql = "insert into student (id,name,age,hiretime,time) " +
                " values(null,'腾讯网吧',30,now(),'2070-12-12 18:59:20')";
        statement.execute(sql);

        System.out.println("insert ok");

        //因为是插入就不要处理结果
        statement.close();
        con.close();
    }



    @Test
    void lsitmap(){
        List<Map<String,Object>> list = new ArrayList<>();
        Map<String,Object> map = new HashMap<>();
        System.out.println(map);
        map.put("id",1);
        System.out.println(map);
        map.put("name","伍鸿杰");
        map.put("age",20);
        System.out.println(map);

        System.out.println(list);
        list.add(map);
        System.out.println(list);
    }
}
